#!/usr/bin/python

from BeautifulSoup import BeautifulSoup
import re
import urllib
from pprint import pprint
import sys
import sqlite3
from datetime import datetime, date, time

NUMDRINKS = 6217
# 6217 as of 1:30am Jan 17 2010

NUMINGREDIENTS = 514
# 514 as of 4:00pm Jan 26, 2011

def parseDrink(drinkNumber):
	sys.stdout.write('%d ' % (drinkNumber))
	sys.stdout.flush()
	id = drinkNumber
	baseUrl = 'http://www.webtender.com/db/drink/'
	html = urllib.urlopen(baseUrl + str(drinkNumber)).read()
	soup = BeautifulSoup(html)
	
	# parse the drink name
	
	title = soup.findAll('h1')[0].contents[0]
	print title
	if len(title.split('410')) > 1:
		print '*** SKIPPED DRINK #%d because it\'s been removed.' % (drinkNumber)
		return None
	
	# parse the ingredients list
	# we get the textual representation of the list, and we parse a list of
	# referenced ingredients is there is a link inside the textual items
	
	allTables = soup.findAll('table')
	drinkTable = allTables[0]
	ingredientTable = drinkTable.findAll('ul')[0].findAll('li')
	ingredientList = []
	position = 0
	for i in ingredientTable:
		try:
			if len(i) > 2:
				amount = i.contents[0]
				detail = i.contents[1].contents[0]
				linkIndex = 1 # see else statement below
			else: # some ingredients don't have a quantity
				amount = None
				detail = i.contents[0].contents[0]
				linkIndex = 0 # is there is no amount, the index of the link is shifted down by one
			# if the ingredient has a link, parse the ingredent id out of its URL
			if len(i.contents[linkIndex]) > 0:
				ingredientLink = i.contents[linkIndex]['href']
				ingredientLinkComponents = ingredientLink.split('/')
				ingredientID = ingredientLinkComponents[len(ingredientLinkComponents)-1]
			ingredientList.append({'amount':amount, 'detail':detail,'id':ingredientID, 'position':position})
			position += 1
		except:
			print '*** SKIPPED INGREDIENT %s with exception:\n%s' % (detail,sys.exc_info())
			continue
			
	# parse the mixing instructions
	
	try:
		mixingInstructions = soup.findAll('p')[1].contents[0]
	except:
		print '*** No mixing instructions'
		mixingInstructions = None
		
	# parse the contributor's comment

	try:
		comment = soup.findAll('p')[2].contents[0]
		if len(comment) < 2:
			comment = None;
	except:
		print '*** No comments'
		comment = None;
			
	# parse the "Drink Information" table
			
	infoRows = drinkTable.findAll('tr')[3:9]
	info = {}
	for row in infoRows:
		header = row.findAll('th')[0].findAll('small')[0].contents[0][:-1]
		column = row.findAll('td')[0]
		small = column.findAll('small')[0]
		item = small.contents[0]
		bold = small.findAll('b')
		links = small.findAll('a')
		if len(bold) > 0:
			item = bold[0].contents[0]
		elif len(links) > 0:
			item = links[0].contents[0]
		# some drinks have no rating -- the rating is being parsed as "vote"
		if header == 'Rating' and item == 'vote':
			print 'old: %s : %s' % (header,item)
			item = None
			print 'new: %s : %s' % (header,item)
		# rating should be parsed into a numeric type
		if header == 'Rating' and item != None:
			item = float(item)
		info[header] = item
		
	return {'id':id, 'name':title, 'ingredients':ingredientList,
	'instructions':mixingInstructions, 'comment':comment, 'info':info}

def parseIngredient(ingredientNumber):
	sys.stdout.write('%d ' % (ingredientNumber))
	sys.stdout.flush()
	id = ingredientNumber
	baseUrl = 'http://www.webtender.com/db/ingred/'
	html = urllib.urlopen(baseUrl + str(ingredientNumber)).read()
	soup = BeautifulSoup(html)
	
	# parse the ingredient title
	
	title = soup.findAll('h1')[0].contents[0]
	print title
	
	# parse the ingredient's description
	
	description = soup.findAll('p')[1].contents[0]
	try:
		description.split('There is currently no information about this ingredient.')
	except:
		# if there was an exception, description is not a string (it's a ta) and split() won't work
		# this (probably) means that it's a bold tag with contents "There is currently no information..."
		# so set the description to None
		# note: i'm sure there as a "more correct" way to do this, but this works.
		description = None
		
	# parse the related drink IDs
	
	drinksUsing = []
	allListItems = soup.findAll('li')
	#print allListItems
	for item in allListItems:
		if len(item.findAll('a')) > 0:
			link = item.contents[1]['href']
			if( len(link.split('/db/drink/')) > 1 ): # filter unwanted list items
				linkComponents = link.split('/')
				dID = linkComponents[len(linkComponents)-1]
				drinksUsing.append(dID)
	
	# parse the "Ingredient Information" table
	
	allTables = soup.findAll('table')
	ingredientTable = allTables[0]
	info = {}
	# we have to look for the appropriate row, since some ingredients have tables
	# inside their description (using <tr>)
	allRows = ingredientTable.findAll('tr')
	foundRow = False
	rowIndex = 3
	while not foundRow and rowIndex < len(allRows)-1:
		row = allRows[rowIndex] # most of the table data is in the SAME row......
		data = row.findAll('dd')
		if len(data) > 1:
			break
		else:
			rowIndex += 1
	category = data[0].contents[1].contents[0]
	alcohol = data[1].contents[0][:-1]
	info['Category'] = category
	info['Alcohol'] = alcohol.strip()
	# get the list of related ingredients
	relatedListItems = row.findAll('li')
	related = []
	for relItem in relatedListItems:
		link = relItem.contents[1]['href']
		linkComponents = link.split('/')
		relID = linkComponents[len(linkComponents)-1]
		related.append(relID)

	return {'id':id, 'name':title, 'description':description, 'info':info,
	'drinksUsing':drinksUsing, 'related':related}	
	
def newDB():
	
	# gets a connection to the new database, and initializes the new schema
	
	dt = datetime.today()
	dbName = 'pappa-bot.%s.db' % (dt.strftime('%d-%m-%y_%M.%I.%S.%p'))
	db = sqlite3.connect(dbName)
	c = db.cursor()
	
	c.execute('''CREATE TABLE drinks (
		drinkID INTEGER,
		name TEXT,
		instructions TEXT,
		comment TEXT,
		rating NUMERIC(1,1),
		category TEXT,
		alcohol TEXT,
		serveIn TEXT,
		PRIMARY KEY (drinkID)
		);''')
		
	c.execute('''CREATE TABLE ingredients (
		ingredientID INTEGER,
		name TEXT,
		description TEXT,
		category TEXT,
		alcohol TEXT,
		PRIMARY KEY (ingredientID)
		);''')
		
	c.execute('''CREATE TABLE drink_ingredients (
		ingredientID INTEGER,
		drinkID INTEGER,
		position INTEGER,
		amount TEXT,
		detail TEXT,
		FOREIGN KEY (ingredientID) REFERENCES ingredients,
		FOREIGN KEY (drinkID) REFERENCES drinks,
		PRIMARY KEY (ingredientID,drinkID,position)
		);''')
		
	c.execute('''CREATE TABLE related_ingredients (
		ingredientID INTEGER,
		relatedIngredientID INTEGER,
		FOREIGN KEY (ingredientID) REFERENCES ingredients,
		FOREIGN KEY (relatedIngredientID) REFERENCES ingredients,
		PRIMARY KEY (ingredientID,relatedIngredientID)
		);''')
	
	db.commit()
	c.close()
	
	print 'Created new database \"%s\".' % (dbName)
	
	return db
	
def addDrinkToDB(db,drink):
	
	# insert the drink into the appropriate tables in the database
	
	c = db.cursor()
	try:
		c.execute('''INSERT INTO drinks(drinkID,name,instructions,comment,rating,category,alcohol,serveIn)
		VALUES(?,?,?,?,?,?,?,?)''',
		(drink['id'],
		drink['name'],
		drink['instructions'],
		drink['comment'],
		drink['info']['Rating'],
		drink['info']['Category'],
		drink['info']['Alcohol'],
		drink['info']['Serve in']))
	except:
		print '*** '+str(sys.exc_info())
		db.commit()
	db.commit()
	
	# add references to all ingredients this drink uses in drink_ingredients
	# (we could also do this in addIngredientToDB() using ingredient['drinksUsing'])
	
	for ingredient in drink['ingredients']:
		try:
			c.execute('''INSERT INTO drink_ingredients(ingredientID,drinkID,position,amount,detail)
			VALUES(?,?,?,?,?)''',
			(ingredient['id'],
			drink['id'],
			ingredient['position'],
			ingredient['amount'],
			ingredient['detail']))
		except:
			print '*** '+str(sys.exc_info())
			continue
	
	return True
	
def addIngredientToDB(db,ingredient):

	# insert the ingredient into the appropriate tables in the database
	
	c = db.cursor()
	try:
		c.execute('''INSERT INTO ingredients(ingredientID,name,description,category,alcohol)
		VALUES(?,?,?,?,?)''',
		(ingredient['id'],
		ingredient['name'],
		ingredient['description'],
		ingredient['info']['Category'],
		ingredient['info']['Alcohol']))
	except:
		print '*** '+str(sys.exc_info())
		db.commit()
	db.commit()
	
	# add refernces to related ingredients in related_ingredients
	
	for related in ingredient['related']:
		try:
			c.execute('''INSERT INTO related_ingredients(ingredientID,relatedIngredientID)
			VALUES(?,?)''',
			(ingredient['id'],
			related))
		except sqlite3.IntegrityError:
			# a pair of related drinks already exists
			continue
		except:
			print '*** '+str(sys.exc_info())
			continue
	
	return True
	
if __name__ == "__main__":
	
	# get a connection a new SQLite database
	
	db = newDB()

	# start drink parser loop, adding drinks to the DB as we get them
	
	print 'Parsing %d drinks...' % (NUMDRINKS)
	drinkNumber = 1
	drinks = []
	while drinkNumber <= NUMDRINKS:
		drink = parseDrink(drinkNumber)
		if drink != None:
			drinks.append(drink)
			if not addDrinkToDB(db,drink):
				print 'Error adding drink #%d to db.' % (drink['id'])
		drinkNumber+=1
			
	# start ingredient parser loop, adding ingredients to the DB as we get them
	
	print '\nParsing %d ingredients...' % (NUMINGREDIENTS)
	ingredientNumber = 1
	ingredients = []
	while ingredientNumber <= NUMINGREDIENTS:
		ingredient = parseIngredient(ingredientNumber)
		if ingredient != None:
			ingredients.append(ingredient)
			if not addIngredientToDB(db,ingredient):
				print 'Error adding ingredient #%d to db.' % (ingredient['id'])
		ingredientNumber+=1
	
	# print the results
			
	num = 1
	print '\nDRINKS:\n'
	for d in drinks:
		print '%d %s\n' % (num,d)
		num+=1
	
	num = 1
	print '\nINGREDIENTS:\n'
	for i in ingredients:
		print '%d %s\n' % (num,i)
		num+=1
		